/*
 * Dao.java
 *
 * Created on 29 de mayo de 2006, 06:32 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package inc.jmc.cyberboard.dao;

import inc.jmc.cyberboard.common.properties.DBConnection;
import inc.jmc.cyberboard.ui.print.ParserPrinterJobs;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.log4j.Logger;
/**
 *
 * @author Juan Miguel Olguin Salguero
 */
public class Dao {
    
    /** Creates a new instance of Dao 
     * @throws ClassNotFoundException 
     * @throws SQLException */
	static Logger logger = Logger.getLogger(Dao.class.getName());
	public static void startupMySql()
	{
		try {
			
		File file1=new File("");
		String localDirectory=""+file1.getCanonicalPath();
		String mysqlPath=localDirectory+"\\mysql";
		ParserPrinterJobs parser= new ParserPrinterJobs(); 
		InputStream inputStream = Runtime.getRuntime().exec(mysqlPath+"\\bin\\mysqld --install-manual MYSQL1").getInputStream();
		String stringFromStream = parser.getStringFromStream(inputStream );
		logger.debug(stringFromStream);
		
		inputStream = Runtime.getRuntime().exec("NET START MYSQL1").getInputStream();
		stringFromStream = parser.getStringFromStream(inputStream );
		logger.debug(stringFromStream);
			
		} catch (IOException e2) {
			
			e2.printStackTrace();
		}
		
		
	}
	public static void shutdownMySql()
	{
		try {
			
		File file1=new File("");
		String localDirectory=""+file1.getCanonicalPath();
		String mysqlPath=localDirectory+"\\mysql";
		ParserPrinterJobs parser= new ParserPrinterJobs();
		InputStream inputStream = Runtime.getRuntime().exec("NET STOP MYSQL1").getInputStream();
		String stringFromStream = parser.getStringFromStream(inputStream );
		logger.debug(stringFromStream);
		inputStream =Runtime.getRuntime().exec(mysqlPath+"\\bin\\mysqld --remove MYSQL1").getInputStream();
		stringFromStream = parser.getStringFromStream(inputStream );
		logger.debug(stringFromStream);
		} catch (IOException e2) {
			
			e2.printStackTrace();
		}
		
		
	}
	
	public static void crearBDPrincipal() throws ClassNotFoundException, SQLException
	{
		DBConnection dbProperties=DBConnection.getInstanceTest();
        Connection conn =null;
        Class.forName(dbProperties.getDriver());
        conn = DriverManager.getConnection(dbProperties.getURLbasicDBMysql());
        String query1="create database ccnetdb";
        PreparedStatement pstmt=conn.prepareStatement(query1);
        pstmt.executeUpdate();
        pstmt.close();
		conn.close();
		
	}
	public static ArrayList<StringBuffer> readSentences() 
	{
		InputStream input = Dao.class.getResourceAsStream("createTablesCcnetdb.sql");
		InputStreamReader auxreader=new InputStreamReader(input);
		BufferedReader reader=new BufferedReader(auxreader);
		//String commands[]=new  String[16];
		ArrayList<StringBuffer> commands =new  ArrayList<StringBuffer>();
		/*for (int i = 0; i < commands.length; i++) {
			commands[i]="";
		}*/
		String line="";
		//int i=0;
		StringBuffer command=new StringBuffer();
		while(line!=null)
		{
			try {
				line=reader.readLine();
				if(line==null)
				{
					break;
				}
				if(line.equals(""))
				{
					//i++;
					commands.add(command);
					command=new StringBuffer();
					
				}
				
				command.append(line+" ");
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return commands;
	}
	public static void crearTablesDBPrincipal() throws ClassNotFoundException, SQLException
	{
		DBConnection dbProperties=DBConnection.getInstance();
        Connection conn =null;
        Class.forName(dbProperties.getDriver());
        conn = DriverManager.getConnection(dbProperties.getUrl());
        ArrayList<StringBuffer> commands=Dao.readSentences();
        for (Iterator<StringBuffer> iterator = commands.iterator(); iterator.hasNext();) {
        	StringBuffer command = iterator.next();
        	logger.info("mysql>"+command.toString());
			PreparedStatement pstmt=conn.prepareStatement(command.toString());
        	pstmt.executeUpdate();
            pstmt.close();
			
		}
        conn.close();
		
	}
    
    public static java.sql.Connection getConnection() throws ClassNotFoundException,java.sql.SQLException
    {
        DBConnection dbProperties=DBConnection.getInstance();
        Connection conn =null;
        Class.forName(dbProperties.getDriver());
        conn = DriverManager.getConnection(dbProperties.getUrl());
        return conn;
    }
    public static java.sql.Connection getConnection(String url) throws ClassNotFoundException,java.sql.SQLException
    {
        DBConnection.getInstance();
        Connection conn =null;
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url);
        return conn;
    }
    public static String getDateTimeDB(java.sql.Connection conn)throws SQLException
    {
        String time1="";
        String query1 ="SELECT DATE_FORMAT(now(), '%d/%m/%Y %H:%i:%s') TIME1";
        PreparedStatement pstmt=conn.prepareStatement(query1);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next())
        {
            time1=rs.getString("TIME1");
        }
        rs.close();
        pstmt.close();
        return time1;
    }
    public static long nextVal(java.sql.Connection conn)throws SQLException
    {
        //String time1="";
        String query1 ="INSERT INTO ccn_sequence(col) VALUES('a')";
        Statement stmt = null;
        ResultSet rs=null;
        stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_UPDATABLE);
        
        stmt.executeUpdate(query1,Statement.RETURN_GENERATED_KEYS);
        long autoIncKeyFromApi = -1;
        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            autoIncKeyFromApi = rs.getInt(1);
        } 
        stmt.executeUpdate("delete from ccn_sequence");
        stmt.close();
        rs.close();
        stmt = null;
        rs = null;
        return autoIncKeyFromApi;
    }
}
